﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;

using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace CEQuery
{
    public partial class DataManipulation : Form
    {        
        public DataManipulation()
        {
            InitializeComponent();
        }
        public DataOperation Operation { get; set; }
        public string TableName { get; set; }
        public string Query { get; private set; }
        public string ConnectionString { get; set; }
        private void DataManipulation_Load(object sender, EventArgs e)
        {
            try
            {
                if (ConnectionString != string.Empty)
                {
                    using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                    {
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = connection;
                        connection.Open();
                        cmd.CommandText = "SELECT Column_name, data_type, is_nullable,'' AS VALUE FROM information_schema.columns WHERE table_Name='" + TableName + "'";

                        SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                        DataSet dsResult = new DataSet();
                        da.Fill(dsResult);
                        dgvTable.DataSource = dsResult.Tables[0];                       
                    }
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                bool isValid = true;
                foreach (DataGridViewRow row in dgvTable.Rows)
                {
                    if (row.Cells[2].Value.ToString().ToUpper() == "NO" && string.IsNullOrEmpty(row.Cells[3].Value.ToString()))
                    {
                        isValid = false;
                        break;
                    }
                }
                if (!isValid)
                {
                    MessageBox.Show("Please enter value for the non-nullable Columns");
                    return;
                }

                btnHidden.PerformClick();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void generateGUIDToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                
                if (ConnectionString != string.Empty)
                {
                    using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                    {
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = connection;
                        connection.Open();
                        cmd.CommandText = "SELECT newid()";
                        dgvTable.SelectedRows[0].Cells[3].Value = cmd.ExecuteScalar().ToString();
                    }
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void dgvTable_MouseUp(object sender, MouseEventArgs e)
        {
            try
            {
                DataGridView.HitTestInfo Hti;
                Hti = dgvTable.HitTest(e.X, e.Y);

                if (e.Button == MouseButtons.Right
                    && Hti.ColumnIndex == 3 &&
                    (dgvTable.Rows[Hti.RowIndex].Cells[1].Value.ToString().ToUpper() == "UNIQUEIDENTIFIER"
                    || dgvTable.Rows[Hti.RowIndex].Cells[1].Value.ToString().ToUpper() == "DATETIME"))
                {
                    if (Hti.Type == DataGridViewHitTestType.Cell)
                    {
                        if (!((DataGridViewRow)(dgvTable.Rows[Hti.RowIndex])).Selected)
                        {
                            dgvTable.ClearSelection();
                            ((DataGridViewRow)dgvTable.Rows[Hti.RowIndex]).Selected = true;
                        }
                        if (dgvTable.Rows[Hti.RowIndex].Cells[1].Value.ToString().ToUpper() == "UNIQUEIDENTIFIER")
                        {
                            ctxGrid.Items[0].Enabled = true;
                            ctxGrid.Items[1].Enabled = false;
                        }
                        else
                        {
                            ctxGrid.Items[0].Enabled = false;
                            ctxGrid.Items[1].Enabled = true;
                        }
                        ctxGrid.Visible = true;
                    }
                }
                else
                {
                    ctxGrid.Visible = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void generateDateTimeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {

                if (ConnectionString != string.Empty)
                {
                    using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                    {
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = connection;
                        connection.Open();
                        cmd.CommandText = "SELECT getdate()";
                        dgvTable.SelectedRows[0].Cells[3].Value = cmd.ExecuteScalar().ToString();
                    }
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void generateInsertQuery()
        {
            Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
            foreach (DataGridViewRow row in dgvTable.Rows)
            {

                keyValuePairs.Add(row.Cells[0].Value.ToString(), row.Cells[3].Value.ToString());
            }
            string cols = string.Empty;
            string vals = string.Empty;
            foreach (string key in keyValuePairs.Keys)
            {
                if (keyValuePairs[key] != string.Empty)
                {
                    cols += cols == string.Empty ? string.Concat("[",key,"]") : string.Concat(",[", key, "]");
                    vals += vals == string.Empty ? string.Concat("'", keyValuePairs[key], "'") : string.Concat(",", "'", keyValuePairs[key], "'");
                }
            }
            Query = string.Concat("INSERT INTO [", TableName, "](", cols, ") VALUES (", vals, ")");
            return;
        }
        private void btnHidden_Click(object sender, EventArgs e)
        {
            try
            {
                generateInsertQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }        
    }
    public enum DataOperation
    {
        INSERT,
        UPDATE,
        DELETE
    }
}
